package cn.cloud9.fix;

import cn.cloud9.po.RegionCell;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import cn.hutool.http.HttpUtil;
import com.alibaba.druid.util.StringUtils;
import lombok.SneakyThrows;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * @author OnCloud9
 * @description
 * @project RegionReptile
 * @date 2022年07月04日 下午 09:21
 */
public class DataFixApplication {
    private static final int TIMEOUT = 3000;

    private static String YEAR;
    private static String HEADER_URL;
    private static String TABLE_NAME;
    public static final String CODE = "CODE";
    public static final String PARENT_CODE = "PARENT_CODE";
    public static final String NAME = "NAME";
    public static final String LEVEL = "LEVEL";
    public static final String TYPE_CODE = "TYPE_CODE";
    public static final String LINK = "LINK";
    public static final String GEN_TIME = "GEN_TIME";
    public static final String REFERER = "Referer";
    public static final String USER_AGENT = "User-Agent";
    public static final String[] BROWSER_AGENTS = {
            "Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Mobile Safari/537.36",
            "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36",
            "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:102.0) Gecko/20100101 Firefox/102.0",
            "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.5060.53 Safari/537.36 Edg/103.0.1264.37",
            "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko"
    };
    public final static Random R = new Random();

    private static Db db = Db.use();

    public static void main(String[] args) {

        fixData("2020");
    }

    public static void fixData(String year) {
        YEAR = year;
        TABLE_NAME = "region" + YEAR;
        HEADER_URL = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/" + YEAR + "/";
        List<RegionCell> regionCells = queryLostData(db, TABLE_NAME, 1);
        int provinceLost = regionCells.size();
        if (!CollUtil.isEmpty(regionCells)) {
            // 递归查询
            regionCells.forEach(DataFixApplication::readDataRecursive);
        }

        regionCells = queryLostData(db, TABLE_NAME, 2);
        int cityLost = regionCells.size();
        if (!CollUtil.isEmpty(regionCells)) {
            // 递归查询
            regionCells.forEach(DataFixApplication::readDataRecursive);
        }
        regionCells = queryLostData(db, TABLE_NAME, 3);
        int countyLost = regionCells.size();
        if (!CollUtil.isEmpty(regionCells)) {
            // 递归查询
            regionCells.forEach(DataFixApplication::readDataRecursive);
        }

        regionCells = queryLostData(db, TABLE_NAME, 4);
        int townLost = regionCells.size();
        if (!CollUtil.isEmpty(regionCells)) {
            // 递归查询
            regionCells.forEach(DataFixApplication::readDataRecursive);
        }
        if (provinceLost + cityLost + countyLost + townLost == 0) {
            System.out.println(YEAR + "年数据补完！");
        }
    }


    private static RegionCell readDataRecursive(RegionCell regionCell) {
        final String regionCellLink = regionCell.getLink();
        if (StringUtils.isEmpty(regionCellLink)) return null;

        final HttpRequest httpRequest = HttpUtil
                .createGet(regionCellLink)
                .timeout(TIMEOUT)
                .setConnectionTimeout(TIMEOUT)
                .setReadTimeout(TIMEOUT);
        if (!CollUtil.isEmpty(regionCell.getCookies())) {
            Map<String, String> headers = new HashMap<>();
            regionCell.getCookies().forEach( cookie -> headers.put("Cookie", cookie.toString() + ";"));
            headers.put(REFERER,  regionCell.getLink());
            headers.put(USER_AGENT, BROWSER_AGENTS[R.nextInt(BROWSER_AGENTS.length)]);
            headers.put("Host", "www.stats.gov.cn");
            headers.put("Upgrade-Insecure-Requests", "1");
            httpRequest.addHeaders(headers);
        }
        final HttpResponse httpResponse = httpRequest.execute();
        // 封装cookie给下一次请求使用
        final RegionCell.RegionCellBuilder builder = RegionCell.builder();
        builder.cookies(httpResponse.getCookies());
        final RegionCell newCell = builder.build();

        if (!httpResponse.isOk()) return null;
        final Document document = Jsoup.parse(httpResponse.body());
        readCityData(document, newCell, regionCell);
        readCountyData(document, newCell, regionCell);
        readTownData(document, newCell, regionCell);
        readVillageData(document, newCell, regionCell);
        return null;
    }

    private static void readVillageData(Document document, RegionCell newCell, RegionCell superCell) {
        Elements villageTrList = document.getElementsByClass("villagetr");
        if (CollUtil.isEmpty(villageTrList)) return;
        villageTrList.forEach(villageTr -> {
            Elements villageTdList = villageTr.getElementsByTag("td");

            Element villageCodeTd = villageTdList.get(0);
            Element villageTypeCodeTd = villageTdList.get(1);
            Element villageNameTd = villageTdList.get(2);

            newCell.setCode(villageCodeTd.text());
            newCell.setParentCode(superCell.getCode());
            newCell.setName(villageNameTd.text());
            newCell.setTypeCode(villageTypeCodeTd.text());
            newCell.setGenTime(LocalDateTime.now());
            writeDataToDb(5, newCell);
        });
    }

    private static void readTownData(Document document, RegionCell newCell, RegionCell superCell) {
        Elements townTrList = document.getElementsByClass("towntr");
        if (CollUtil.isEmpty(townTrList)) return;
        townTrList.forEach(townTr -> {

            Elements townTds = townTr.getElementsByTag("td");
            Element townCodeTd = townTds.get(0);
            Element townNameTd = townTds.get(1);
            Elements aTagsInTownCodeTd = townCodeTd.getElementsByTag("a");
            Elements aTagsInTownNameTd = townNameTd.getElementsByTag("a");
            // 取城乡代码
            String regionTownCode;
            String townNextHref = null;

            boolean isTownLinkTag = !CollUtil.isEmpty(aTagsInTownCodeTd);
            if (isTownLinkTag) {
                regionTownCode = aTagsInTownCodeTd.get(0).text();
                townNextHref = aTagsInTownCodeTd.get(0).attr("href");
            } else regionTownCode = townCodeTd.text();
            String regionTownName;
            if (!CollUtil.isEmpty(aTagsInTownNameTd)) regionTownName = aTagsInTownNameTd.get(0).text();
            else regionTownName = townNameTd.text();

            final String codePrefix = regionTownCode.substring(0, 2);
            boolean condition1 = "44".equals(codePrefix) || "46".equals(codePrefix);

            if (condition1 && isTownLinkTag) {
                final String link = superCell.getLink();
                final String basePath = link.substring(0, link.lastIndexOf("/") + 1);
                String url = basePath + townNextHref;
                newCell.setLink(url);
            } else if (isTownLinkTag) {
                int index = townNextHref.indexOf("/");
                String provincePath = townNextHref.substring(index + 1, index + 3);
                String cityPath = "/" + townNextHref.substring(index + 3, index + 5) + "/";
                String url = HEADER_URL + provincePath + cityPath + townNextHref;
                newCell.setLink(url);
            }

            newCell.setCode(regionTownCode);
            newCell.setParentCode(superCell.getCode());
            newCell.setName(regionTownName);
            newCell.setGenTime(LocalDateTime.now());
            writeDataToDb(4, newCell);
            readDataRecursive(newCell);
        });
    }

    private static void readCountyData(Document document, RegionCell newCell, RegionCell superCell) {
        Elements countyTrList = document.getElementsByClass("countytr");
        if (CollUtil.isEmpty(countyTrList)) return;
        countyTrList.forEach(countyTr -> {
            Elements countyTds = countyTr.getElementsByTag("td");
            Element countyCodeTd = countyTds.get(0);
            Element countyNameTd = countyTds.get(1);
            Elements aTagsInCountyCodeTd = countyCodeTd.getElementsByTag("a");
            Elements aTagsInCountyNameTd = countyNameTd.getElementsByTag("a");
            // 取城乡代码
            String regionCountyCode;
            String countyNextHref = null;

            boolean isCountyLinkTag = !CollUtil.isEmpty(aTagsInCountyCodeTd);
            if (isCountyLinkTag) {
                regionCountyCode = aTagsInCountyCodeTd.get(0).text();
                countyNextHref = aTagsInCountyCodeTd.get(0).attr("href");
            } else regionCountyCode = countyCodeTd.text();

            String regionCountyName;
            if (!CollUtil.isEmpty(aTagsInCountyNameTd)) regionCountyName = aTagsInCountyNameTd.get(0).text();
            else regionCountyName = countyNameTd.text();

            if (isCountyLinkTag) {
                int index = countyNextHref.indexOf('/');
                String provincePath = countyNextHref.substring(index + 1, index + 3) + "/";
                String countyUrl = HEADER_URL + provincePath + countyNextHref;
                newCell.setLink(countyUrl);
            }

            newCell.setCode(regionCountyCode);
            newCell.setParentCode(superCell.getCode());
            newCell.setName(regionCountyName);
            newCell.setGenTime(LocalDateTime.now());
            writeDataToDb(3, newCell);
            readDataRecursive(newCell);
        });
    }

    private static void readCityData(Document document, RegionCell newCell, RegionCell superCell) {
        final Elements citytrList = document.getElementsByClass("citytr");
        if (CollUtil.isEmpty(citytrList)) return;
        citytrList.forEach(cityTr -> {
            Elements cityTds = cityTr.getElementsByTag("td");
            Element codeTd = cityTds.get(0);
            Element nameTd = cityTds.get(1);
            Elements asTagInCodeTd = codeTd.getElementsByTag("a");
            Elements asTagInNameTd = nameTd.getElementsByTag("a");

            String regionCityCode;
            String cityNextHref = null;

            boolean isLinkTag = !CollUtil.isEmpty(asTagInCodeTd);
            if (isLinkTag) {
                regionCityCode = asTagInCodeTd.get(0).text();
                cityNextHref = asTagInCodeTd.get(0).attr("href");
            } else
                regionCityCode = codeTd.text();
            String regionName;
            if (!CollUtil.isEmpty(asTagInNameTd)) regionName = asTagInNameTd.get(0).text();
            else regionName = nameTd.text();

            if (isLinkTag) newCell.setLink(HEADER_URL + cityNextHref);

            newCell.setCode(regionCityCode);
            newCell.setParentCode(superCell.getCode());
            newCell.setName(regionName);
            newCell.setGenTime(LocalDateTime.now());
            writeDataToDb(2, newCell);
            readDataRecursive(newCell);
        });
    }

    private static void writeDataToDb(int level, RegionCell cell) {
        try {
            db.insertOrUpdate(Entity
                    .create(TABLE_NAME)
                    .set(CODE, cell.getCode())
                    .set(PARENT_CODE, cell.getParentCode())
                    .set(NAME, cell.getName())
                    .set(LEVEL, level)
                    .set(TYPE_CODE, cell.getTypeCode())
                    .set(LINK, cell.getLink())
                    .set(GEN_TIME, cell.getGenTime()),
                    CODE
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * SELECT
     *  SUPER.*
     * FROM
     *  (SELECT * FROM region2021 WHERE `LEVEL` = 4) AS SUPER
     *  LEFT JOIN (SELECT * FROM region2021) AS SUB ON SUPER.CODE = SUB.PARENT_CODE
     *  WHERE SUB.PARENT_CODE IS NULL AND SUPER.LINK IS NOT NULL
     * @param db
     * @param tableName
     * @param level
     * @return
     */
    @SneakyThrows
    private static List<RegionCell> queryLostData(
        Db db,
        String tableName,
        int level
    ) {
        String sql = "SELECT * FROM " + tableName + " WHERE LEVEL = ? ";
//        String sql =
//        "SELECT \n" +
//        "\tSUPER.*\n" +
//        "FROM \n" +
//        "\t(SELECT * FROM " + tableName + " WHERE `LEVEL` = ? ) AS SUPER\n" +
//        "\tLEFT JOIN (SELECT * FROM " + tableName + " ) AS SUB ON SUPER.CODE = SUB.PARENT_CODE\n" +
//        "\tWHERE SUB.PARENT_CODE IS NULL AND SUPER.LINK IS NOT NULL";
        return db.query(sql, RegionCell.class, level);
    }

}
